﻿--use PolePosition
GO


IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_WorkQueue')
	DROP PROCEDURE up_WorkQueue
GO

CREATE PROCEDURE up_WorkQueue
	@UtilityFk int
AS

DECLARE @WorkQueueResultTypes varchar(255)
DECLARE @WorkQueueIncludeElectrical bit

SELECT @WorkQueueResultTypes = SettingValue
FROM tb_Utility_Settings
WHERE 
	SettingName = 'WorkQueueResultTypes'
	AND UtilityFk = @UtilityFk


SELECT @WorkQueueIncludeElectrical = SettingValue
FROM tb_Utility_Settings
WHERE 
	SettingName = 'WorkQueueIncludeElectrical'
	AND UtilityFk = @UtilityFk


DECLARE @ResultTypeFilter table
(
	ResultType int PRIMARY KEY
)

if (@WorkQueueResultTypes IS NULL)
BEGIN

	INSERT @ResultTypeFilter
	SELECT Vendor_InspVisit_XmissionStructures_ResultTypeId
	FROM tb_Vendor_InspVisit_XmissionStructures_ResultTypes  
END
ELSE
BEGIN
	INSERT @ResultTypeFilter
	SELECT DISTINCT value FROM dbo.fn_Split(@WorkQueueResultTypes, ',')
END

IF (@WorkQueueIncludeElectrical IS NULL)
	SET @WorkQueueIncludeElectrical = 1


SELECT 
	 Project.[Name] AS Project
	,ProjectTypeFk
	,Pole.Vendor_InspVisit_XMissionStructure_PoleId AS VisitNumber
	,ResultType.Name as InspectionResult
	,CASE WHEN Result.bElectrical = 1 THEN 'Yes' ELSE 'No' END AS ElectricalProblem
	,Result.Comments
	,dbo.fn_VendorXmissionInspection_GetNoteString(Result.Vendor_InspVisit_XmissionStructures_ResultId) AS InspectionNotes
	,Map.[Name] AS Map
	,Pole.SectionFk AS [Section]
	,TopType.[Name] AS TopType
	,NULL AS D804
	,Pole.AcquisitionDate
	,Pole.Longitude
	,Pole.Latitude
	--,InspectionType.[Name] as InspectionType
	,Remedy.Utility_Asset_XMissionStructure_Pole_CrossRemedy_Vendor_InspectionVisitId AS RemedyPrimaryKeyValue
--	,Remedy.Vendor_InspVisit_XMissionStructure_PoleFK AS VisitPoleFk
--	,Remedy.Utility_Asset_XMissionStructureFK AS StructureFk
--	,Remedy.DiscoveredDate
	,Remedy.RemedyStatusFK
	,RemedyStatus.Name AS RemedyStatusText
	,Remedy.RemedyAssigned
	,Remedy.RemedyLastAction
	,Remedy.RemedyResolvedDate
	,Remedy.RemedyNotes
	,'Xmission' AS WorkQueueType
	--Temporary chnage that we pull pole ids rather than structure ids
	,Pole.Vendor_InspVisit_XMissionStructure_PoleId AS KmlId
--	,Vendor_InspVisit_XMissionStructureFk AS KmlId
	,CONVERT(varchar(50), NULL) as SaveGuid
FROM tb_Vendor_InspVisit_XmissionStructures_Poles Pole 
INNER JOIN tb_Vendor_Projects Project 
	ON Pole.Vendor_ProjectFK = Project.Vendor_ProjectID
INNER JOIN tb_Vendor_InspVisit_XmissionStructures_Results Result
	ON Pole.Vendor_InspVisit_XMissionStructure_PoleId = Result.Vendor_InspVisit_XMissionStructure_PoleFk
LEFT JOIN @ResultTypeFilter ResultTypeFilter
	ON ResultTypeFilter.ResultType = Result.Vendor_InspVisit_XmissionStructures_ResultTypeFk
LEFT JOIN tb_Vendor_InspVisit_XmissionStructures_ResultTypes ResultType 
	ON ResultType.Vendor_InspVisit_XmissionStructures_ResultTypeId = Result.Vendor_InspVisit_XmissionStructures_ResultTypeFk
--LEFT JOIN tb_Vendor_InspVisit_XmissionStructures_Inspection_Type InspectionType 
--	ON Result.Vendor_InspVisit_XmissionStructures_Inspection_TypeFk = InspectionType.Vendor_InspVisit_XmissionStructures_Inspection_TypeId
LEFT JOIN tb_XMissionStructures_PoleTopTypes TopType
	ON TopType.XMissionStructures_PoleTopTypeId = Pole.TopTypeFk
LEFT JOIN tb_Utility_Polygons Map
	ON Pole.MapFk = Map.Utility_PolygonId
LEFT JOIN tb_Utility_Asset_XMissionStructure_Poles_CrossRemedy_Vendor_InspectionVisit Remedy
	ON Pole.Vendor_InspVisit_XMissionStructure_PoleId = Remedy.Vendor_InspVisit_XMissionStructure_PoleFK
LEFT JOIN tb_RemedyStatus RemedyStatus
	ON RemedyStatus.RemedyStatusId = Remedy.RemedyStatusFK
	
WHERE 
	Project.UtilityFk = @UtilityFk
	AND ((@WorkQueueIncludeElectrical = 1 AND @WorkQueueIncludeElectrical = Result.bElectrical)
		OR
	    (NOT ResultTypeFilter.ResultType IS NULL))
--ORDER BY Pole.Vendor_InspVisit_XMissionStructure_PoleId
UNION ALL
SELECT 
	 Project.[Name] AS Project
	,ProjectTypeFk
	,Pole.Vendor_Distribution_Poles_PoleId AS VisitNumber
	,ResultType.Name as InspectionResult
	,CASE WHEN Result.bElectrical = 1 THEN 'Yes' ELSE 'No' END AS ElectricalProblem
	,Result.Comments
	,dbo.fn_VendorDistributionPole_GetNoteString(Pole.Vendor_Distribution_Poles_PoleId) AS InspectionNotes
	,Map.[Name] AS Map
	,Pole.SectionFk AS [Section]
	,dbo.fn_DistributionPole_GetTopTypeString(Pole.Vendor_Distribution_Poles_PoleId, 0) AS TopType
	,NULL AS D804
	,Pole.AcquisitionDate
	,Pole.Longitude
	,Pole.Latitude
	--,InspectionType.[Name] as InspectionType
	,Remedy.RemedyId AS RemedyPrimaryKeyValue
--	,Remedy.Vendor_InspVisit_XMissionStructure_PoleFK AS VisitPoleFk
--	,Remedy.Utility_Asset_XMissionStructureFK AS StructureFk
--	,Remedy.DiscoveredDate
	,Remedy.RemedyStatusFK
	,RemedyStatus.Name AS RemedyStatusText
	,Remedy.RemedyAssigned
	,Remedy.RemedyLastAction
	,Remedy.RemedyResolvedDate
	,Remedy.RemedyNotes
	,'Distribution' AS WorkQueueType
	,Vendor_Distribution_Poles_PoleId AS KmlId
	,CONVERT(varchar(50), NULL) as SaveGuid
FROM tb_Vendor_Distribution_Poles Pole 
INNER JOIN tb_Vendor_Projects Project 
	ON Pole.ProjectFk = Project.Vendor_ProjectID
INNER JOIN tb_Vendor_Distribution_InspectionResults Result
	ON Pole.Vendor_Distribution_Poles_PoleId = Result.Vendor_Distribution_PoleFk
LEFT JOIN @ResultTypeFilter ResultTypeFilter
	ON ResultTypeFilter.ResultType = Result.Vendor_Distribution_ResultTypeFk
LEFT JOIN tb_Vendor_InspVisit_XmissionStructures_ResultTypes ResultType 
	ON ResultType.Vendor_InspVisit_XmissionStructures_ResultTypeId = Result.Vendor_Distribution_ResultTypeFk


LEFT JOIN tb_Utility_Polygons Map
	ON Pole.MapFk = Map.Utility_PolygonId
LEFT JOIN tb_Vendor_Distribution_Pole_Remedy Remedy
	ON Pole.Vendor_Distribution_Poles_PoleId = Remedy.PoleFk
LEFT JOIN tb_RemedyStatus RemedyStatus
	ON RemedyStatus.RemedyStatusId = Remedy.RemedyStatusFK
	
WHERE 
	Project.UtilityFk = @UtilityFk
	AND ((@WorkQueueIncludeElectrical = 1 AND @WorkQueueIncludeElectrical = Result.bElectrical)
		OR
	    (NOT ResultTypeFilter.ResultType IS NULL))
ORDER BY 1, 3


GO


